/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Gruppe 4
 */
public class DBSalesLineItem implements IFDBSalesLineItem{
    
    private  Connection con;

    public DBSalesLineItem() {
        con = DbConnection1.getInstance().getDBcon();
    }
    
    // get SalesLineItems
    @Override
    public ArrayList<SalesLineItem> getSalesOrders(int orderId, boolean retriveAssociation)
    {
        String wClause = "  orderId = '" + orderId + "'";
        return miscWhere(wClause, retriveAssociation);
    }
    
    //insert a new sales line item
    @Override
    public int insertSalesLineItem(SalesLineItem saleItem)
    {
        //call to get the next line item id
        int nextLineItemId = GetMax.getMaxId("Select max(lineItemId) from SalesLineItem");
        nextLineItemId = nextLineItemId + 1;
        System.out.println("next line item id = " +  nextLineItemId);
  
       int rc = -1;
	   String query="INSERT INTO SalesLineItem(lineItemId, amount, productId, orderId)  VALUES('"+
                        nextLineItemId + "','" +
                        saleItem.getAmount()  + "','"  +
		    	saleItem.getProduct().getProductId()  + "','"  +
                        saleItem.getSalesOrder().getOrderId() + "')";              
           
      System.out.println("insert : " + query);		  					
      try{ // insert new line item
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
		}//end try	
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in sales line item db: "+ex);
	   }
	   return(rc);
    }
    
    private ArrayList<SalesLineItem> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<SalesLineItem> list = new ArrayList<SalesLineItem>();	
		
	    String query =  buildQuery(wClause);
            System.out.println(query);
  
            try{ // read the line item from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
		while( results.next() ){
	     	 SalesLineItem saleItemObj = new SalesLineItem();
		 saleItemObj = buildSalesLineItem(results);	
                 list.add(saleItemObj);	
		}//end while
                 stmt.close();     
                 if(retrieveAssociation)
                 {   //The Product is to be build as well
                     for(SalesLineItem saleItemObj : list){
                        
                        IFDBPro dbProductObj = new DBProduct();
                        Product productObj = dbProductObj.findProduct(saleItemObj.getProduct().getProductId(), false);
                        System.out.println("Product is seleceted ");
                        saleItemObj.setProduct(productObj);
                     
                     }
                 }//end if   
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
    
    //method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM SalesLineItem";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
        
        //method to build a line item object
	private SalesLineItem buildSalesLineItem(ResultSet results)
      {   SalesLineItem saleItemObj = new SalesLineItem();
          try{
                saleItemObj.setLineItemId(results.getInt("lineItemId"));
                saleItemObj.setAmount(results.getInt("amount"));
                saleItemObj.setProduct(new Product(results.getInt("productId")));
                saleItemObj.setSalesOrder(new SalesOrder(results.getInt("orderId")));     
          }
         catch(Exception e)
         {
             System.out.println("error in building the sales line item object");
         }
         return saleItemObj;
      }
}

